﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Ruandao.MiniAccess.SQLite3
{
    using Ruandao.MiniAccess.OrMap;
    /// <summary>
    /// 根据OR映射数据创建Sqlite3中的SQL语句。
    /// </summary>
    public class SqlBuilder
    {
        #region 创建SQL语句的公有成员

        /// <summary>
        /// 构建插入记录的Insert语句。
        /// </summary>
        /// <param name="tableName">表名。</param>
        /// <param name="columnMaps">提供OR映射信息。</param>
        /// <returns>INSERT 语句的文本。</returns>
        public static string BuildInsertSql(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO [" + tableName + "] (\r\n");

            // insert 列表
            bool isFirst = true;
            foreach (ColumnMap cm in columnMaps)
            {
                if (cm.IsInsert)
                {
                    if (!isFirst) sb.Append(",\r\n");
                    sb.Append("\t" + ColumnNameString(cm.ColumnName));
                    if (isFirst) isFirst = false;
                }
            }
            sb.Append("\r\n)\r\nVALUES\r\n(\r\n");

            // 值的列表
            isFirst = true;
            foreach (ColumnMap cm in columnMaps)
            {
                if (cm.IsInsert)
                {
                    if (!isFirst) sb.Append(",\r\n");
                    sb.Append("\t" + ParameterString(cm.ColumnName));
                    if (isFirst) isFirst = false;
                }
            }
            sb.Append("\r\n)\r\n");

            return sb.ToString();
        }

        /// <summary>
        /// 构建根据主键值删除一条记录的DELETE语句。
        /// </summary>
        /// <param name="tableName">表名。</param>
        /// <param name="columnMaps">提供OR映射信息。</param>
        /// <returns>DELETE语句的文本。</returns>
        /// <remarks>
        /// 如果表没有主键，将抛出异常。
        /// </remarks>
        public static string BuildDeleteSql(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE");

            sb.Append(" FROM [" + tableName + "]\r\n");
            sb.Append(WhereByKeysString(tableName, columnMaps));
            return sb.ToString();
        }

        /// <summary>
        /// 构建根据条件删除数据库记录的DELETE语句。
        /// </summary>
        /// <param name="tableName">表名。</param>
        /// <param name="columnMaps">提供OR映射信息。</param>
        /// <returns>DELETE语句的文本。</returns>
        /// <remarks>条件参数的参数名称缺省使用 @Conditions。执行时@Conditions参数的值必须是一个合法的WHERE子句，以及后面可以有连接、OrderBy等字句。</remarks>
        public static string BuildDeleteByConditionsSql(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE\r\n");
            sb.Append("\tFROM [" + tableName + "]");

            return sb.ToString();
        }

        /// <summary>
        /// 构建根据主键更新记录的UPDATE语句。
        /// </summary>
        /// <param name="tableName">表名。</param>
        /// <param name="columnMaps">提供OR映射信息。</param>
        /// <returns>UPDATE语句的文本。</returns>
        /// <remarks>
        /// 如果表没有主键，或者表中没有可更新的字段，都将抛出异常。
        /// </remarks>
        public static string BuildUpdateSql(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE" + " [" + tableName + "]\r\nSET\r\n");

            string prefix = "";
            bool isFirst = true;
            foreach (ColumnMap cm in columnMaps)
            {
                if (cm.IsUpdate)
                {
                    if (!isFirst) sb.Append(",\r\n");
                    if (cm.ColumnMapType == ColumnMapType.PrimaryKeyMap)
                    {
                        prefix = "New_";
                    }
                    else
                    {
                        prefix = "";
                    }
                    sb.Append("\t" + ColumnNameString(cm.ColumnName) + " = " + ParameterString(cm.ColumnName, prefix));
                    if (isFirst) isFirst = false;
                }
            }

            if (isFirst == true)
            {
                throw new OrMapException("表 <" + tableName + "> 没有可以更新的字段。");
            }

            sb.Append("\r\n");
            sb.Append(WhereByKeysString(tableName, columnMaps));
            return sb.ToString();
        }

        /// <summary>
        /// 根据主键查找记录的SQL 语句。
        /// </summary>
        /// <param name="tableName">表名。</param>
        /// <param name="columnMaps">提供OR映射信息。</param>
        /// <returns>SELECT 语句的文本。</returns>
        /// <remarks>
        /// 如果表没有主键，将抛出异常。
        /// </remarks>
        public static string BuildSelectByKeysSql(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(GetSelectString(tableName, columnMaps));

            sb.Append("FROM [" + tableName + "]\r\n");
            sb.Append(WhereByKeysString(tableName, columnMaps));
            return sb.ToString();
        }

        /// <summary>
        /// 构造按条件查询的Select语句。
        /// </summary>
        /// <param name="tableName">表名。</param>
        /// <param name="columnMaps">提供OR映射信息。</param>
        /// <returns>SELECT 语句的文本。</returns>
        /// <remarks>条件参数的参数名称缺省使用 @Conditions。执行时@Conditions参数的值必须是一个合法的WHERE子句，以及后面可以有连接、OrderBy等字句。</remarks>
        public static string BuildSelectByConditionsSql(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(GetSelectString(tableName, columnMaps));

            sb.Append("\tFROM [" + tableName + "]");
            return sb.ToString();
        }

        #endregion

        #region 私有工具方法

        // 字符串开始无空格，字符串以回车换行结尾。
        private static string GetSelectString(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT\r\n");
            bool isFirst = true;
            foreach (ColumnMap cm in columnMaps)
            {
                if (!isFirst) sb.Append(",\r\n");
                sb.Append("\t[" + tableName + "]." + ColumnNameString(cm.ColumnName));
                if (isFirst) isFirst = false;
            }
            sb.Append("\r\n");
            return sb.ToString();
        }

        // 字符串开始无空格，字符串以回车换行结尾。如果没有主键，将抛出异常。
        private static string WhereByKeysString(string tableName, IList<ColumnMap> columnMaps)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("WHERE\r\n");
            bool isFirst = true;
            foreach (ColumnMap cm in columnMaps)
            {
                if (cm.ColumnMapType == ColumnMapType.PrimaryKeyMap)
                {
                    if (!isFirst) sb.Append(" AND\r\n");
                    sb.Append("\t" + ColumnNameString(cm.ColumnName) + " = "+ParameterString(cm.ColumnName));
                    if (isFirst) isFirst = false;
                }
            }
            if (isFirst)
            {
                throw new OrMapException("表" + tableName + "没有主键。当使用GetEntity, DeleteEntity和UpdateEntity等函数时，对应的数据库表必须有主键。");
            }
            sb.Append("\r\n");
            return sb.ToString();
        }

        private static string ColumnNameString(string column, string prefix)
        {
            return "[" + prefix + column + "]";
        }

        private static string ColumnNameString(string column)
        {
            return ColumnNameString(column, "");
        }

        // 把空格替换为下划线。因为参数名称不支持空格分隔的名称。
        private static string ParameterString(string column, string prefix)
        {
            return "@" + prefix + String.Join("_", column.Split(' '));
        }

        private static string ParameterString(string column)
        {
            return ParameterString(column, "");
        }

        #endregion

    }
}
